package com.plugin.manage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.plugin.core.model.entity.ElderDish;
import com.plugin.core.model.entity.ElderDishRecord;
import com.plugin.core.model.entity.ElderMealSet;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 用餐看板Mapper接口
 * 
 * @author mark
 * @date 2025-07-26
 */
@Mapper
public interface MealsMapper extends BaseMapper<ElderDishRecord> {

    /**
     * 统计今日用餐人数
     * 
     * @return 今日用餐人数
     */
    @Select("SELECT COUNT(DISTINCT elder_id) " +
            "FROM elder_dish_record " +
            "WHERE DATE(dinner_time) = CURDATE() " +
            "AND del = 0")
    Long countTodayMeals();

    /**
     * 查询今日菜谱记录
     * 
     * @return 今日菜谱记录列表
     */
    @Select("SELECT id, dish_name, category_id, price " +
            "FROM elder_dish " +
            "WHERE del = 0 AND DATE(create_time) = CURDATE()" +
            "ORDER BY create_time DESC " +
            "LIMIT 5")
    List<ElderDish> getTodayMenuRecords();

    /**
     * 统计最近7天每日的就餐人次
     * 
     * @return 最近7天每日的就餐人次统计
     */
    @Select("SELECT DATE(dinner_time) as meal_date, COUNT(*) as meal_count " +
            "FROM elder_dish_record " +
            "WHERE DATE(dinner_time) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) " +
            "AND DATE(dinner_time) <= CURDATE() " +
            "GROUP BY DATE(dinner_time) " +
            "ORDER BY meal_date ASC")
    List<Map<String, Object>> getLast7DaysMealCounts();

    /**
     * 获取所有启用的今日套餐列表
     * 
     * @return 所有启用的今日套餐列表
     */
    @Select("SELECT id, set_name, create_time " +
            "FROM elder_meal_set " +
            "WHERE del = 0 " +
            "ORDER BY  create_time DESC")
    List<ElderMealSet> getTodayMenu();


    /**
     * 统计今日就餐方式数量（堂食、外带、送餐）
     * 根据elder_dish_record中的dinner_mode字段统计各种就餐方式的记录数
     * 
     * @return 今日就餐方式统计
     */
    @Select("SELECT dinner_mode, COUNT(*) as count " +
            "FROM elder_dish_record " +
            "WHERE DATE(dinner_time) = CURDATE() " +
            "AND del = 0 " +
            "GROUP BY dinner_mode " +
            "ORDER BY dinner_mode ASC")
    List<Map<String, Object>> getTodayDinnerModeStatistics();

    /**
     * 统计今日菜品分类数量和百分比（荤菜、素菜、主食、汤品等）
     * 统计dish_ids中每个菜品ID在今天被售卖的总次数，按elder_dish_category分类计算比值
     * 
     * @return 今日菜品分类统计
     */
    @Select("SELECT edc.category_name, " +
            "       SUM(CASE WHEN edr.id IS NOT NULL AND FIND_IN_SET(ed.id, edr.dish_ids) > 0 THEN 1 ELSE 0 END) as count " +
            "FROM elder_dish ed " +
            "LEFT JOIN elder_dish_category edc ON ed.category_id = edc.id " +
            "LEFT JOIN elder_dish_record edr ON DATE(edr.dinner_time) = CURDATE() " +
            "                                AND edr.del = 0 " +
            "                                AND FIND_IN_SET(ed.id, edr.dish_ids) > 0 " +
            "WHERE (ed.del = 0 OR ed.del IS NULL) " +
            "AND (edc.del = 0 OR edc.del IS NULL) " +
            "AND edc.category_name IS NOT NULL " +
            "GROUP BY edc.category_name " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getTodayDishCategoryStatistics();

    /**
     * 统计今日菜品消费份数TOP5
     * 根据elder_dish_record中的dish_ids统计每个菜品在今天的出现次数，按消费次数排序取前5名
     * 
     * @return 今日菜品消费份数TOP5
     */
    @Select("SELECT ed.id, ed.dish_name, " +
            "       SUM(CASE WHEN edr.id IS NOT NULL AND FIND_IN_SET(ed.id, edr.dish_ids) > 0 THEN 1 ELSE 0 END) as count " +
            "FROM elder_dish ed " +
            "LEFT JOIN elder_dish_record edr ON DATE(edr.dinner_time) = CURDATE() " +
            "                                AND edr.del = 0 " +
            "                                AND FIND_IN_SET(ed.id, edr.dish_ids) > 0 " +
            "WHERE (ed.del = 0 OR ed.del IS NULL) " +
            "GROUP BY ed.id, ed.dish_name " +
            "HAVING count > 0 " +
            "ORDER BY count DESC " +
            "LIMIT 5")
    List<Map<String, Object>> getTodayDishConsumptionTop5();

    /**
     * 统计今日套餐消费次数TOP5
     * 统计elder_dish_record表中del=0且is_set=1的记录数量，按count排序取前5名
     * 
     * @return 今日套餐消费次数TOP5
     */
    @Select("SELECT dish_name, COUNT(*) as count " +
            "FROM elder_dish_record " +
            "WHERE DATE(dinner_time) = CURDATE() " +
            "AND del = 0 " +
            "AND is_set = 1 " +
            "GROUP BY dish_name " +
            "ORDER BY count DESC " +
            "LIMIT 5")
    List<Map<String, Object>> getTodayMealSetTop5();
}
